package com.example.homework.controller;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import static java.lang.Math.min;

public class Customer {
    private final String dbUrl =
            "jdbc:sqlserver://localhost:1433;database=homework;trustServerCertificate=true";
    private final String userName = "sa";
    private final String userPwd = "bjdjr0544";
    private Connection con;
    private Statement st;
    private ResultSet rs;
    private String sql;

    public Map<String, Object> customerIn(String name) {
        int state = 0;
        int shop = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Buser where Bname = '" + name +
                    "' and Bstatus = N'已审核' and Bdel = 0";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                shop = rs.getInt("Bid");
            } else {
                state = 1;
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        map.put("shop", shop);
        return map;
    }

    public ArrayList<Map<String, Object>> customerAllorder(int cid) {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Cid = " + cid +
                    " and Ostatus != N'未通过' and Odel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("order", String.valueOf(rs.getInt("Oid")));
                map.put("time", rs.getDate("Otime") + " " + rs.getTime("Otime"));
                map.put("value", rs.getString("Oval"));
                ans.add(map);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> customerOneorder(int cid, String order) {
        Map<String, Object> map = new HashMap<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Order_shop where Cid = " + cid + " and Oid = "
                    + Integer.parseInt(order) + " and Ostatus != N'未通过' and Odel = 0";
            rs = st.executeQuery(sql);
            if (!rs.next()) {
                map.put("state", 1);
                map.put("order", "0");
                map.put("time", "2023-1-1 0:0:0");
                map.put("shop", "None");
                map.put("cust", "None");
                map.put("value", "0");
                map.put("foods", new ArrayList<>());
                map.put("phone", "0");
                map.put("status", "未接单");
                map.put("tip", "None");
            } else {
                map.put("state", 0);
                map.put("order", String.valueOf(rs.getInt("Oid")));
                map.put("time", rs.getDate("Otime") + " " + rs.getTime("Otime"));
                map.put("value", String.valueOf(rs.getFloat("Oval")));
                map.put("status", rs.getString("Ostatus"));
                map.put("tip", rs.getString("Otip"));
                String temp = "select * from Buser where Bid = " + rs.getInt("Bid");
                ResultSet tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("shop", tempRs.getString("Bname"));
                    map.put("phone", tempRs.getString("Btele"));
                } else {
                    map.put("phone", "0");
                    map.put("shop", "None");
                }
                temp = "select * from Cuser where Cid = " + cid;
                tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("cust", tempRs.getString("Cname"));
                } else {
                    map.put("cust", "None");
                }
                temp = "select * from Order_food where Oid = " + rs.getInt("Oid");
                tempRs = st.executeQuery(temp);
                ArrayList<Map<String, Object>> foods = new ArrayList<>();
                while (tempRs.next()) {
                    Map<String, Object> subMap = new HashMap<>();
                    subMap.put("id", String.valueOf(tempRs.getInt("Fid")));
                    subMap.put("food", tempRs.getString("Fname"));
                    subMap.put("img", tempRs.getBytes("Fimg"));
                    subMap.put("num", tempRs.getInt("Fnum"));
                    subMap.put("price", String.valueOf(tempRs.getFloat("Fvalue")));
                    foods.add(subMap);
                }
                map.put("foods", foods);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return map;
    }

    public Map<String, Object> makeFoodMap(int choice) { // 0异常初始化，1根据rs
        Map<String, Object> map = new HashMap<>();
        if (choice == 0) {
            map.put("state", 1);
            map.put("id", "0");
            map.put("shop", "None");
            map.put("food", "None");
            map.put("price", "0");
            map.put("img", new byte[0]);
            map.put("desc", "None");
            map.put("stock", 0);
            map.put("type", "None");
            map.put("rec", 0);
            map.put("status", "下架");
        } else {
            try {
                map.put("state", 0);
                map.put("id", String.valueOf(rs.getInt("Fid")));
                map.put("food", rs.getString("Fname"));
                map.put("price", String.valueOf(rs.getFloat("Fprice")));
                map.put("img", rs.getBytes("Fimg"));
                map.put("desc", rs.getString("Fdesc"));
                map.put("stock", rs.getInt("Fstock"));
                map.put("type", rs.getString("Ftype"));
                map.put("rec", rs.getInt("Frec"));
                map.put("status", rs.getString("Fstatus"));
                String temp = "select * from Buser where Bid = " + rs.getInt("Bid");
                ResultSet tempRs = st.executeQuery(temp);
                if (tempRs.next()) {
                    map.put("shop", tempRs.getString("Bname"));
                } else {
                    map.put("shop", "None");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return map;
    }

    public Map<String, Object> customerOnefood(int bid, String food, String id) {
        Map<String, Object> map = new HashMap<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            if (!id.equals("null")) {
                sql = "select * from Food where Fid = " + Integer.parseInt(id) + " and Bid = "
                        + bid + " and Fpass = N'已审核' and Fdel = 0 and Fstatus = N'上架'";
            } else {
                sql = "select * from Food where Fname = '" + food + "' and Bid = "
                        + bid + " and Fpass = N'已审核' and Fdel = 0 and Fstatus = N'上架'";
            }
            rs = st.executeQuery(sql);
            if (rs.next()) {
                map = makeFoodMap(1);
            } else {
                map = makeFoodMap(0);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return map;
    }

    public ArrayList<Map<String, Object>> customerAllfood(int bid) {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Bid = " + bid +
                    " and Fpass = N'已审核' and Fdel = 0 and Fstatus = N'上架'";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                ans.add(makeFoodMap(1));
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public ArrayList<Map<String, Object>> customerAllshop() {
        ArrayList<Map<String, Object>> ans = new ArrayList<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Buser where Bstatus = N'已审核' and Bdel = 0";
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("tele", rs.getString("Btele"));
                map.put("name", rs.getString("Bname"));
                map.put("img", rs.getBytes("Bimg"));
                map.put("addr", rs.getString("Baddr"));
                ans.add(map);
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return ans;
    }

    public Map<String, Object> customerOneshop(String name) {
        Map<String, Object> map = new HashMap<>();
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Buser where Bname = '" + name +
                    "' and Bstatus = N'已审核' and Bdel = 0";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                map.put("state", 0);
                map.put("tele", rs.getString("Btele"));
                map.put("name", rs.getString("Bname"));
                map.put("pwd", "******");
                map.put("img", rs.getBytes("Bimg"));
                map.put("addr", rs.getString("Baddr"));
            } else {
                map.put("state", 1);
                map.put("tele", "0");
                map.put("name", "None");
                map.put("pwd", "******");
                map.put("img", new byte[0]);
                map.put("addr", "None");
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return map;
    }

    public Map<String, Object> customerBuild(int bid, int cid) {
        int state = 0;
        try {
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Cart where Bid = " + bid + " and Cid = " + cid;
            rs = st.executeQuery(sql);
            if (rs.next()) {
                state = 1;
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public Map<String, Object> customerAdd(int bid, int cid, String name) {
        int state = 0;
        try {
            updateCart(bid, cid);
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Bid = " + bid + " and Fname = '" + name + "' " +
                    "and Fdel = 0 and Fpass = N'已审核' and Fstatus = N'上架'";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                int stock = rs.getInt("Fstock");
                if (stock == 0) {
                    state = 1;
                } else {
                    int fid = rs.getInt("Fid");
                    stock = stock - 1;
                    sql = "update Food set Fstock = " + stock + " where Fid = " + fid;
                    st.executeUpdate(sql);
                    sql = "select * from Cart where Cid = " + cid +
                            " and Bid = " + bid + " and Fid = " + fid;
                    rs = st.executeQuery(sql);
                    if (rs.next()) {
                        int num = rs.getInt("Fnum") + 1;
                        sql = "update Cart set Fnum = " + num +
                                " where Cid = " + cid + " and Bid = " + bid + " and Fid = " + fid;
                        st.executeUpdate(sql);
                    } else {
                        sql = "insert into Cart values (" + bid + ", " + cid + ", " + fid + ", 1)";
                        st.executeUpdate(sql);
                    }
                    sql = "insert into Record(Rtype, Rtime, Rid, Fid, Bid) values " +
                            "(9, getdate(), " + cid + ", " + fid + ", " + bid + ")";
                    st.executeUpdate(sql);
                }
            } else {
                state = 1;
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }

    public void updateCart(int bid, int cid) { // 购物车同步商家的下架或删除操作
        try {
            sql = "select * from Cart where Cid = " + cid + " and Bid = " + bid;
            rs = st.executeQuery(sql);
            while (rs.next()) {
                int fid = rs.getInt("Fid");
                sql = "select * from Food where Fid = " + fid +
                        " and Fdel = 0 and Fpass = N'已审核' and Fstatus = N'上架'";
                ResultSet temp = st.executeQuery(sql);
                if (!temp.next()) {
                    int num = rs.getInt("Fnum");
                    sql = "select Fstock from Food where Fid = " + fid;
                    temp = st.executeQuery(sql);
                    int stock = min(num + temp.getInt("Fstock"), 100);
                    sql = "update Food set Fstock = " + stock + " where Fid = " + fid;
                    st.executeUpdate(sql);
                    sql = "delete from Cart where Cid = " + cid +
                            " and Bid = " + bid + " and Fid = " + fid;
                    st.executeUpdate(sql);
                }
            }
        }  catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
    }

    public Map<String, Object> customerDecrease(int bid, int cid, String name) {
        try {
            updateCart(bid, cid);
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Food where Bid = " + bid + " and Fname = '" + name + "' " +
                    "and Fdel = 0 and Fpass = N'已审核' and Fstatus = N'上架'";
            rs = st.executeQuery(sql);
            if (rs.next()) {
                int fid = rs.getInt("Fid");
                int stock = rs.getInt("Fstock");
                sql = "select * from Cart where Cid = " + cid +
                        " and Bid = " + bid + " and Fid = " + fid;
                rs = st.executeQuery(sql);
                if (rs.next()) {
                    int num = rs.getInt("Fnum") - 1;
                    if (num == 0) {
                        sql = "delete from Cart where Cid = " + cid +
                                " and Bid = " + bid + " and Fid = " + fid;
                        st.executeUpdate(sql);
                    } else {
                        sql = "update Cart set Fnum = " + num +
                                " where Cid = " + cid + " and Bid = " + bid + " and Fid = " + fid;
                        st.executeUpdate(sql);
                    }
                    sql = "insert into Record(Rtype, Rtime, Rid, Fid, Bid) values " +
                            "(10, getdate(), " + cid + ", " + fid + ", " + bid + ")";
                    st.executeUpdate(sql);
                    if (stock < 100) {
                        stock++;
                        sql = "update Food set Fstock = " + stock + " where Fid = " + fid;
                        st.executeUpdate(sql);
                    }
                }
            }
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", 0);
        return map;
    }

    public Map<String, Object> customerGet(int bid, int cid) {
        Map<String, Object> map = new HashMap<>();
        map.put("state", 1);
        map.put("value", "0");
        map.put("foods", new ArrayList<>());
        try {
            updateCart(bid, cid);
            con = DriverManager.getConnection(dbUrl, userName, userPwd);
            st = con.createStatement();
            sql = "select * from Cart where Cid = " + cid + " and Bid = " + bid;
            rs = st.executeQuery(sql);
            float value = 0;
            ArrayList<Map<String, Object>> foods = new ArrayList<>();
            while (rs.next()) {
                sql = "select * from Food where Fid = " + rs.getInt("Fid") +
                        " and Fdel = 0 and Fpass = N'已审核' and Fstatus = N'上架'";
                ResultSet temp = st.executeQuery(sql);
                if (temp.next()) {
                    Map<String, Object> subMap = new HashMap<>();
                    subMap.put("id", temp.getInt("Fid"));
                    subMap.put("food", temp.getString("Fname"));
                    subMap.put("img", temp.getBytes("Fimg"));
                    subMap.put("num", rs.getInt("Fnum"));
                    subMap.put("price", String.valueOf(
                            temp.getFloat("Fprice") * rs.getInt("Fnum")));
                    value += temp.getFloat("Fprice") * rs.getInt("Fnum");
                    foods.add(subMap);
                }

            }
            map.replace("state", 0);
            map.replace("value", value);
            map.replace("foods", foods);
            rs.close();
            con.close();
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        return map;
    }

    public Map<String, Object> customerSubmit(int bid, int cid, String tips) {
        int state = 0;
        try {
            updateCart(bid, cid);
            sql = "select * from Cart where Cid = " + cid + " and Bid = " + bid;
            rs = st.executeQuery(sql);
            int flag = 0;
            int oid = 0;
            float value = 0;
            while (rs.next()) {
                if (flag == 0) {
                    flag = 1;
                    sql = "select Omax from Id_max where temp = 0";
                    rs = st.executeQuery(sql);
                    rs.next();
                    oid = rs.getInt("Omax") + 1;
                    sql = "update Id_max set Omax = " + oid + " where temp = 0";
                    st.executeUpdate(sql);
                }
                int fid = rs.getInt("Fid");
                sql = "select * from Food where Fid = " + fid +
                        " and Fdel = 0 and Fpass = N'已审核' and Fstatus = N'上架'";
                ResultSet temp = st.executeQuery(sql);
                if (temp.next()) {
                    PreparedStatement prest = con.prepareStatement("insert into Order_food values ("
                            + oid + ", " + fid + ", '" + temp.getString("Fname") + "', ?, "
                            + rs.getInt("Fnum") + ", "
                            + rs.getInt("Fnum") * temp.getFloat("Fprice") + ")");
                    value += rs.getInt("Fnum") * temp.getFloat("Fprice");
                    Blob blob = con.createBlob();
                    blob.setBytes(1, temp.getBytes("Fimg"));
                    prest.setBlob(1, blob);
                    prest.executeUpdate();
                    sql = "delete from Cart where Cid = " + cid +
                            " and Bid = " + bid + " and Fid = " + fid;
                    st.executeUpdate(sql);
                }
            }
            if (flag == 0) {
                state = 1;
            } else {
                sql = "select Ctele from Cuser where Cid = " + cid;
                rs = st.executeQuery(sql);
                rs.next();
                String ctele = rs.getString("Ctele");
                sql = "insert into Order_shop values (" + oid + ", getdate(), '" + ctele + "', "
                        + bid + ", " + cid + ", " + value + ", N'未通过', '" + tips + "', 0)";
                st.executeUpdate(sql);
                sql = "insert into Record(Rtype, Rtime, Rid, Oid, Bid, Otip) values " +
                        "(8, getdate(), " + cid + ", " + oid + ", " + bid + ", '" + tips + "')";
                st.executeUpdate(sql);
            }
        }  catch (SQLException e) {
            e.printStackTrace();
            System.out.println("数据库连接失败！！！");
        }
        Map<String, Object> map = new HashMap<>();
        map.put("state", state);
        return map;
    }
}
